﻿using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace TestDrivenDevelopment.Fixture
{
    public class SQLHelper
    {
        public static string ConnectionString
        {
            get
            {
                return ConfigurationManager.ConnectionStrings[Environment.MachineName].ConnectionString;
            }
        }

        public static void ClearTable(string table)
        {
            string sql = string.Format("DELETE [{0}]", table);
            ExecuteNonQueryCommand(sql);
        }

        public static int CountRecordInTable(string table)
        {
            string sql = string.Format("SELECT COUNT(*) FROM [{0}]", table);
            return (int)ExecuteScalarQueryCommand(sql);
        }

        public static object ExecuteScalarQueryCommand(string sql)
        {
            using (IDbConnection connection = new SqlConnection(ConnectionString))
            {
                connection.Open();

                using (IDbCommand cmd = connection.CreateCommand())
                {
                    cmd.CommandText = sql;
                    Console.WriteLine("SQLHelper {0}", sql);
                    try
                    {
                        object result = cmd.ExecuteScalar();
                        return result;
                    }
                    catch (Exception e)
                    {
                        Console.WriteLine(e);
                        throw new ApplicationException("ExecuteScalarQueryCommand", e);
                    }
                }
            }
        }

        public static void ExecuteNonQueryCommand(string sql)
        {
            using (IDbConnection connection = new SqlConnection(ConnectionString))
            {
                connection.Open();
                using (IDbCommand cmd = connection.CreateCommand())
                {
                    cmd.CommandText = sql;
                    Console.WriteLine("SQLHelper {0}", sql);
                    cmd.ExecuteNonQuery();
                }
            }
        }

        //public static DataTable ExecuteQuery(string sql)
        //{
        //    var table = new DataTable();
        //    using (IDbConnection connection = new SqlConnection(ConnectionString))
        //    {
        //        connection.Open();
        //        using (IDbCommand cmd = connection.CreateCommand())
        //        {
        //            cmd.CommandText = sql;
        //            Console.WriteLine("SQLHelper {0}", sql);
        //            cmd.CommandType = CommandType.Text;
        //            using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
        //            {
        //                table.Load(reader);
        //            }
        //        }
        //    }
        //    return table;
        //}
    }
}
